package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.ProductCatalogSpecDAO;
import com.fly.estore.domain.ProductCatalogSpec;
import com.fly.estore.domain.ProductSpec;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/19.
 */
@Repository("ProductCatalogSpecDAO")
public class ProductCatalogSpecDAOImpl extends BaseDAO<ProductCatalogSpec> implements ProductCatalogSpecDAO {

    public int addProductCatalogSpec(final ProductCatalogSpec catalogSpec) {
        int update = 0;
        String sql = "insert into product_catalog_spec(product_catalog_id, product_spec_id) values(?, ?)";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, catalogSpec.getProductCatalogId());
                ps.setString(2, catalogSpec.getProductSpec().getProductSpecId());
            }
        });
        return update;
    }

    public int deleteByCatalogId(final String productCatalogId) {
        int update = 0;
        String sql = "delete from product_catalog_spec where product_catalog_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, productCatalogId);
            }
        });
        return update;
    }

    public List<ProductCatalogSpec> getByCatalogId(String productCatalogId) {
        List<ProductCatalogSpec> catalogSpecList = new ArrayList<ProductCatalogSpec>();
        String sql = "select product_catalog_id, ps.product_spec_id, ps.spec_name, ps.spec_value from product_catalog_spec pc inner join product_spec ps on ps.product_spec_id = pc.product_spec_id where pc.product_catalog_id = ?";
        catalogSpecList = getJdbcTemplate().query(sql, new CatalogSpecRowMapper(), new Object[]{productCatalogId});
        return catalogSpecList;
    }

    public  class CatalogSpecRowMapper implements RowMapper<ProductCatalogSpec>{
        public ProductCatalogSpec mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductCatalogSpec catalogSpec = new ProductCatalogSpec();
            catalogSpec.setProductCatalogId(rs.getString("product_catalog_id"));
            ProductSpec productSpec = new ProductSpec();
            productSpec.setProductSpecId(rs.getString("product_spec_id"));
            productSpec.setSpecName(rs.getString("spec_name"));
            productSpec.setSpecValues(rs.getString("spec_value"));
            catalogSpec.setProductSpec(productSpec);
            return catalogSpec;
        }
    }
}
